package com.webapp.model;

import java.sql.Date;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

import com.mysql.jdbc.Statement;

public class LoginModel {
	
	Statement stmt = DatabaseConnector.connect();
	
	//person data
	private int social;
	private String lastName;
	private String firstName;
	private String address;
	private String city;
	private String state;
	private int zipCode;
	private int phoneNum;
	private String email;
	private String password;
	private String regType;
	
	//user data
	private int userId;
	private Date creationDate;
	private int rating;
	
	//manager / employee data
	private Date startDate;
	private int hourlyRate;
	
	//employee data
	private int manager = -1;
	
	//checks to see if user exists
	public String checkAuthentication(String nEmail, String nPassword) {
		try{
			ResultSet rs;
			
			if((stmt.executeQuery("SELECT * FROM persons WHERE Email_Address = '" +nEmail+ "' AND password = '" +nPassword+ "'")).next()){
				rs = stmt.executeQuery("SELECT * FROM persons WHERE Email_Address = '" +nEmail+ "' AND password = '" +nPassword+ "'");
				rs.next();
				social = rs.getInt(1);
				
				//gathers all 'persons' data, which is in common between user, manager and employee
				lastName = rs.getString(2);
				firstName = rs.getString(3);
				address = rs.getString(4);
				city = rs.getString(5);
				state = rs.getString(6);
				zipCode = rs.getInt(7);
				phoneNum = rs.getInt(8);
				email = rs.getString(9);
				password = nPassword;
				
				//person is a user if TRUE
				if((stmt.executeQuery("SELECT * FROM users WHERE SSN = '" +social+ "'")).next()){
					rs = stmt.executeQuery("SELECT * FROM users WHERE SSN = '" +social+ "'");
					rs.next();
					
					//gather all data for a user
					userId = rs.getInt(2);
					creationDate = rs.getDate(3);
					rating = rs.getInt(4);
					regType = "user";
				}
				//person is an employee if TRUE
				else if((stmt.executeQuery("SELECT * FROM employees WHERE SSN = '" +social+ "'")).next()){
					rs = stmt.executeQuery("SELECT * FROM employees WHERE SSN = '" +social+ "'");
					rs.next();
					
					//gather all data for an employee
					startDate = rs.getDate(2);
					hourlyRate = rs.getInt(3);
					manager = rs.getInt(4);
					regType = "employee";
					
				}
				//person is a manager if TRUE
				else if((stmt.executeQuery("SELECT * FROM managers WHERE SSN = '" +social+ "'")).next()){
					rs = stmt.executeQuery("SELECT * FROM managers WHERE SSN = '" +social+ "'");
					rs.next();
					
					//gathers all data for a manager
					startDate = rs.getDate(2);
					hourlyRate = rs.getInt(3);
					regType = "manager";
				}
				//person exists, but is not a user, employee or manager making some sort of error, related to the database.
				else{
					return "error";
				}
				
				stmt.close();
				
				return "true";
			}
			else{
				stmt.close();
				return "false";
			}
		}
		catch(Exception ex){
			System.out.println("Could not connect to database");
			return "error";
		}
	}
	
	public Object getUser(){
		
		//person must be a user, create a user object
		if(regType.equals("user")){
			User user = new User(social);
			user.setLastName(lastName);
			user.setFirstName(firstName);
			user.setAddress(address);
			user.setCity(city);
			user.setState(state);
			user.setZip(zipCode);
			user.setPhone(phoneNum);
			user.setEmail(email);
			user.setUserId(userId);
			user.setCreationDate(creationDate);
			user.setRating(rating);
			user.setType("user");
			
			return user;
		}
		//person must be a manager, create a manager object
		else if(regType.equals("manager")){
			Manager manager = new Manager(social); 
			manager.setLastName(lastName);
			manager.setFirstName(firstName);
			manager.setAddress(address);
			manager.setCity(city);
			manager.setState(state);
			manager.setZip(zipCode);
			manager.setPhone(phoneNum);
			manager.setEmail(email);
			manager.setStartDate(startDate);
			manager.setRate(hourlyRate);
			manager.setType("manager");
			
			return manager;
		}
		//person must be an employee, create an employee object
		else if(regType.equals("employee")){
			Employee employee = new Employee(social);
			employee.setLastName(lastName);
			employee.setFirstName(firstName);
			employee.setAddress(address);
			employee.setCity(city);
			employee.setState(state);
			employee.setZip(zipCode);
			employee.setPhone(phoneNum);
			employee.setEmail(email);
			employee.setStartDate(startDate);
			employee.setRate(hourlyRate);
			employee.setManager(manager);
			employee.setType("employee");
			
			return employee;
		}
		else{
			return null;
		}
	}
	
	public boolean registerUser(List list){
		
		try{
			if(!(stmt.executeQuery("SELECT * FROM persons WHERE Email_Address = '" +list.get(8)+ "'")).next()){
				stmt.executeQuery("INSERT INTO persons (SSN, Last_Name, First_Name, Address, City,"+
						"State, Zip_Code, Telephone, Email_Address, password) VALUES ('" +list.get(9)+ "', '"+
						list.get(2)+ "', '" +list.get(1)+ "', '" +list.get(3)+ "', '" +list.get(4)+ "', '" +list.get(5)+ "', '" +
						list.get(6)+ "', '" +list.get(7)+ "', '" +list.get(8)+ "', '" +list.get(10)+ "')");
				
				Calendar cal = Calendar.getInstance();
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String current = sdf.format(cal.getTime());
				
				if(list.get(0).equals("user")){
					int id;
					System.out.print("here user");
					while(true){
						id = new Double( Math.random() * 1000000000 ).intValue();
						if(!(stmt.executeQuery("SELECT User_Id FROM users WHERE User_Id = '" +id+ "'")).next()){
							break;
						}
					}
					stmt.executeQuery("INSERT INTO users (SSN, User_Id, Account_Creation) VALUES ('" +list.get(9)+ "', '"+
									id+ "', '" +current+ "')");
				}
				else if(list.get(0).equals("employee")){
					stmt.executeQuery("INSERT INTO employees (SSN, Start_Date) VALUES ('" +list.get(9)+ "', '"+
									current+ "')");
				}
				else if(list.get(0).equals("manager")){
					stmt.executeQuery("INSERT INTO managers (SSN, Start_Date) VALUES ('" +list.get(9)+ "', '"+
							current+ "')");
				}
				
				stmt.close();
			}
			//user already exists, cannot register as a user
			else{
				stmt.close();
				return false;
			}
			return true;
		}catch(Exception ex){return false;}
	}
}